'''
Created on June 23, 2011

@author: yc30rr1
'''
import pyExcelerator as xl #@UnresolvedImport
from win32com.client import constants,Dispatch
import re
import os
from ctypes.wintypes import RGB
import pythoncom
from logging import getLogger
log = getLogger(__name__)
borderTop = 3
borderBottom = 4
borderLeft = 1
borderRight = 2
borderSolid = 1
borderDashed = 2
borderDotted = 3
colorBlack = 1
directionUp = -4162
directionDown = -4121
directionLeft = -4131
directionRight = -4152
from pywin.framework.editor import color
class ExcelReporting(object):
    mydoc=""
        
    def addRow(self,sourceFilepath,sheet_name,values,row):
        try:
            self.open(sourceFilepath)
            self.sheet = self.sheets('Result')
            self.sheet.Activate()
            col=0
            colVal=0   
            for col in range(len(values)):
                c1 = col + 1
                if values[col]=="PASS":
                    colVal=c1
                    self.sheet.Cells(row,colVal).value ="'"+str(re.sub(r'[\'+\[\]]','',values[col]))
                    self.sheet.Cells(row,colVal).font.bold=True
                    self.sheet.Cells(row,colVal).font.colorindex=4
#                    self.setBorder(self.sheet.Cells(row,colVal),4,1,1)
                elif values[col]=="FAIL":
                    colVal=c1
                    self.sheet.Cells(row,colVal).value ="'"+str(re.sub(r'[\'+\[\]]','',values[col]))
                    self.sheet.Cells(row,colVal).font.bold=True
                    self.sheet.Cells(row,colVal).font.colorindex=3
#                    self.setBorder(self.sheet.Cells(row,colVal),4,1,1)
                else:
                    self.sheet.Cells(row,c1).EntireColumn.AutoFit()
                    self.sheet.Cells(row,c1).value = "'"+str(re.sub(r'[\'+\[\]]','',values[col]))
                    self.sheet.Cells(row,c1).EntireColumn.AutoFit()
#                    self.setBorder(self.sheet.Cells(row,c1),4,1,1)
                
            #End For
            self.close()
        except:
            
            log.warning("Exception in adding row to excel")
            self.close()

    def createExcelFile(self,sourceFilepath,sheet_name,headers,rowval):
        
        try:
            
            mydoc=xl.Workbook()
            mysheet=mydoc.add_sheet(sheet_name)  
            header_font=xl.Font() #make a font object
            header_font.bold=True
#            header_font.underline=True
            header_style = xl.XFStyle()
            header_style.font = header_font
            
            for col,value in enumerate(headers):
                
                mysheet.write(rowval,col,value,header_style)
                #write values and highlight those that match my criteria
                highlighted_row_font=xl.Font() #no real highlighting available?
                highlighted_row_font.bold=True
                highlighted_row_font.colour_index=2 #2 is red,
                highlighted_row_style = xl.XFStyle(); highlighted_row_style.font = highlighted_row_font
                mydoc.save(sourceFilepath)
        except :
            log.debug('Unable to create Excel File')
    
    def open(self,sourceFilepath):
        self.excel = Dispatch('Excel.Application')
        self.workbook = self.excel.Workbooks.Open(sourceFilepath)
        self.workBook = self.excel.ActiveWorkbook
        self.activeSheet = self.excel.ActiveSheet
        self.sheets = self.workBook.Sheets
              
    def close(self):
         self.workBook.Saved = 0 
         self.workBook.Close(SaveChanges=1) 
         self.excel.Quit()
         self.excel.Visible = 0
         
    def setBorder(self, range, side, line_style=borderSolid, color=colorBlack):

            range = self.getRange(range).Borders(side)
            range.LineStyle = line_style
            range.Color = color

    def getRange(self, range):
        """
        Get a range object for the specified range or single cell.
        """
        return self.excel.ActiveWorkbook.Sheets(self.sheet).Range(range)

            
            
    
    